/*
* Conditions Of Use
*
* This software was developed by employees of the Sigmatrix(Beijing) Corporation.
* This software is provided by sigmatrix as a service and is expressly
* provided "AS IS."  Sigmatrix MAKES NO WARRANTY OF ANY KIND, EXPRESS, IMPLIED
* OR STATUTORY, INCLUDING, WITHOUT LIMITATION, THE IMPLIED WARRANTY OF
* MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE, NON-INFRINGEMENT
* AND DATA ACCURACY.  Sigmatrix does not warrant or make any representations
* regarding the use of the software or the results thereof, including but
* not limited to the correctness, accuracy, reliability or usefulness of
* the software.
*
* Permission to use this software is contingent upon your acceptance
* of the terms of this agreement.
*
*/
package com.sigmatrix.h5.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
import org.springframework.util.CollectionUtils;

import com.sigmatrix.h5.dao.WarehouseStockDao;
import com.sigmatrix.h5.dao.impl.common.impl.BaseDaoImpl;
import com.sigmatrix.h5.dto.SqlEntity;
import com.sigmatrix.h5.dto.StockSyncDto;
import com.sigmatrix.h5.entity.WarehouseStock;
import com.sigmatrix.h5.enums.TableEnum;
import com.sigmatrix.h5.exception.DaoException;
import com.sigmatrix.h5.utils.Utils;

/**
 * @ClassName: WarehouseStockDaoImpl
 * @Description: 库存信息数据库操作接口实现
 * @author youjun 
 * @date 2017-06-14 09:50:39
 */
@Repository(value="warehouseStockDao")
public class WarehouseStockDaoImpl extends BaseDaoImpl<WarehouseStock> implements WarehouseStockDao {
	
	private static final Logger LOGGER = LoggerFactory.getLogger(WarehouseStockDaoImpl.class);
	
	
	/**
	 * (non-Javadoc)
	 *@Title: findByCondition
	 *@Description: 
	 *@see com.sigmatrix.h5.dao.WarehouseStockDao#findByCondition(java.lang.String, java.lang.Integer, java.lang.Integer, java.lang.Integer)
	 *@date 2017年9月28日 下午3:57:23
	 */
	@Override
	public List<WarehouseStock> findByCondition(final String eseCode,
			final Integer warehouseId,final Integer productId,final Integer level) throws DaoException {
		final StringBuilder sql = new StringBuilder()
				.append("SELECT * FROM ")
				.append(getTname(eseCode, TableEnum.WAREHOUSE_STOCK))
				.append(" WHERE _warehouse_id = ").append(warehouseId)
				.append(" AND _product_id = ").append(productId)
				.append(" AND _level = ").append(level);
		return jdbcTemplate.query(sql.toString(), new WarehouseStockRowMapper());
	}
	/**
	 * (non-Javadoc)
	 *@Title: findByCondition
	 *@Description: 
	 *@see com.sigmatrix.h5.dao.WarehouseStockDao#findByCondition(java.lang.String, java.lang.Integer, java.lang.Integer, java.lang.Integer, java.lang.Integer)
	 *@author gaomingkai
	 *@date 2018年1月30日 下午10:10:40
	 */
	@Override
	public List<WarehouseStock> findByCondition(String eseCode, Integer warehouseId, Integer productId,
			Integer dealearId, Integer level) throws DaoException {
		final StringBuilder sql = new StringBuilder()
				.append("SELECT * FROM ")
				.append(getTname(eseCode, TableEnum.WAREHOUSE_STOCK))
				.append(" WHERE _warehouse_id = ").append(warehouseId)
				.append(" AND _product_id = ").append(productId)
				.append(" AND _channel_id = ").append(dealearId)
				.append(" AND _level = ").append(level);
		return jdbcTemplate.query(sql.toString(), new WarehouseStockRowMapper());
	}
	
	
	/**
	 * (non-Javadoc)
	 *@Title: findPartByWarehouseId
	 *@Description: 
	 *@see com.sigmatrix.sc.distribution.app.inter.dao.StockDao#findPartByWarehouseId(java.lang.String, java.util.List)
	 *@author 
	 *@date 2017年9月14日 下午9:51:06
	 */
	public List<StockSyncDto> findPartByWarehouseId(final String eseCode,final List<Integer> warehouseList) throws DaoException {
		if(Utils.isEmpty(eseCode)||Utils.isEmpty(warehouseList)){
			throw new DaoException("请求参数有误");
		}
		final StringBuilder sql = new StringBuilder()
		.append("SELECT _product_id, _product_code, _number,_level FROM ")
		.append(getTname(eseCode, TableEnum.WAREHOUSE_STOCK))
		.append(" WHERE _warehouse_id in (");
		
		final StringBuilder inSql = new StringBuilder();
		for (Integer warehouseId : warehouseList) {
			inSql.append(warehouseId).append(",");
		}
		sql.append(inSql.substring(0, inSql.length()-1))//仓库集合
		.append(")");
		LOGGER.info(sql.toString());
		try {
			return jdbcTemplate.query(sql.toString(), new StockSyncDtoRowMapper());
		} catch (DataAccessException e) {
			throw new DaoException(e);
		}
	}
	
	/**
	 *@ClassName: StockSyncDtoRowMapper
	 *@Description: 
	 *@date 2017年9月14日 下午9:20:23
	 */
	public class StockSyncDtoRowMapper implements RowMapper<StockSyncDto> {
		public StockSyncDto mapRow(final ResultSet rs, final int rowNum) throws SQLException {
			final StockSyncDto obj = new StockSyncDto();
			obj.setProductId(rs.getInt("_product_id"));
			obj.setProductCode(rs.getString("_product_code"));
			obj.setNumber(rs.getInt("_number"));
			obj.setLevel(rs.getInt("_level"));
			return obj;
		}
	}


	/**
	 * (non-Javadoc)
	 * @Title: findById
	 * @Description: 
	 * @see com.sigmatrix.sc.production.mgt.dao.WarehouseStockDao#findById(java.lang.Integer)
	 * @author youjun
	 * @date 2017年6月14日 上午10:30:45
	 */
	@Override
	public WarehouseStock findById(final Integer id, final String enterpriseCode) throws DaoException {
		final StringBuilder sql = new StringBuilder("SELECT * FROM ").append(getTname(enterpriseCode, TableEnum.WAREHOUSE_STOCK)).append(" WHERE _id = ?");
		final Object[] params = new Object[] { id };
		final List<WarehouseStock> list = jdbcTemplate.query(sql.toString(), params, new WarehouseStockRowMapper());
		if (CollectionUtils.isEmpty(list)) {
			return null;
		}
		return list.get(0);
	}

	/**
	 * (non-Javadoc)
	 * @Title: save
	 * @Description: 
	 * @see com.sigmatrix.sc.production.mgt.dao.WarehouseStockDao#save(com.sigmatrix.sc.production.mgt.entity.WarehouseStock)
	 * @author youjun
	 * @date 2017年6月14日 上午10:30:55
	 */
	@Override
	public int save(final WarehouseStock warehouseStock, final String enterpriseCode) throws DaoException {
		SqlEntity  sqlEntity = this.getInsertSql(enterpriseCode, warehouseStock);
		return jdbcTemplate.update(sqlEntity.getSql(), sqlEntity.getParam(), sqlEntity.getParamType());
	}
	
	/**
	 * (non-Javadoc)
	 *@Title: insert
	 *@Description: 
	 *@see com.sigmatrix.h5.dao.WarehouseStockDao#insert(java.lang.String, com.sigmatrix.h5.entity.WarehouseStock)
	 *@author 
	 *@date 2017年9月28日 下午5:39:32
	 */
	public Long insert(final String eseCode,final WarehouseStock warehouseStock) throws DaoException {
		final SqlEntity  sqlEntity = this.getInsertSql(eseCode, warehouseStock);
		return this.saveReturnKey(sqlEntity.getSql(),sqlEntity.getParam());
	}
	
	/**
	 *@Title: getInsertSql
	 *@Description: 拼接insert sql
	 *@return SqlEntity
	 *@date 2017年9月28日 下午5:26:20
	 *@param eseCode
	 *@param warehouseStock
	 */
	private SqlEntity getInsertSql(final String eseCode,final WarehouseStock warehouseStock){
		final StringBuilder sql = new StringBuilder("INSERT INTO  ")
				.append(getTname(eseCode, TableEnum.WAREHOUSE_STOCK))
				.append(" (_channel_id, _warehouse_id, _product_id, _product_code, _number, _level) ")
				.append(" VALUES (?, ?, ?, ?, ?, ?)");
		final Object[] params = new Object[]{
				warehouseStock.getChannelId(), warehouseStock.getWarehouseId(), 
				warehouseStock.getProductId(), warehouseStock.getProductCode(), 
				warehouseStock.getNumber(), warehouseStock.getLevel()};
		final int[] argTypes = new int[]{
				Types.INTEGER, Types.INTEGER, 
				Types.INTEGER, Types.VARCHAR, 
				Types.INTEGER, Types.INTEGER
				};
		
		SqlEntity  sqlEntity = new SqlEntity();
		sqlEntity.setParamType(argTypes);
		sqlEntity.setParam(params);
		sqlEntity.setSql(sql.toString());
		return sqlEntity;
	}

	/**
	 * (non-Javadoc)
	 * 
	 * @Title: update
	 * @Description: 
	 * @see com.sigmatrix.sc.production.mgt.dao.WarehouseStockDao
	 *      #update(com.sigmatrix.sc.production.mgt.entity.WarehouseStock)
	 * @author youjun
	 * @date 2017年6月14日 上午10:31:02
	 */
	@Override
	public int update(final WarehouseStock warehouseStock, final String enterpriseCode) throws DaoException {
		final StringBuilder sql = new StringBuilder(
				"UPDATE  ").append(getTname(enterpriseCode, TableEnum.WAREHOUSE_STOCK))
				.append("  SET _channel_id = ?, _warehouse_id = ?, _product_id = ?, _product_code = ?, _number = ?, _level = ? WHERE _id = ?");
		final Object[] params = new Object[]{warehouseStock.getChannelId(), warehouseStock.getWarehouseId(), warehouseStock.getProductId(), warehouseStock.getProductCode(), warehouseStock.getNumber(), warehouseStock.getLevel(), warehouseStock.getId()};
		final int[] argTypes = new int[]{Types.INTEGER, Types.INTEGER, Types.INTEGER, Types.VARCHAR, Types.INTEGER, Types.INTEGER, Types.INTEGER};
		return jdbcTemplate.update(sql.toString(), params, argTypes);
	}
	
	
	public int updateNumById(final String eseCode,final Long id,final Integer num){
		final StringBuilder sql = new StringBuilder()
				.append("UPDATE ")
				.append(getTname(eseCode, TableEnum.WAREHOUSE_STOCK))
				.append(" SET _number = ?")
				.append(" WHERE _id = ?");
		final Object[] params = new Object[]{num,id};
		final int[] argTypes = new int[]{Types.INTEGER,Types.BIGINT};
		return jdbcTemplate.update(sql.toString(),params,argTypes);
	}
	
	/**
	 * (non-Javadoc)
	 *@Title: reduceStockNum
	 *@Description: 
	 *@see com.sigmatrix.sc.handheld.inter.dao.WarehouseStockDao#reduceStockNum(int, int, int, int, java.lang.String)
	 *@author youjun
	 *@date 2017年8月8日 上午1:40:11
	 */
	@Override
	public int reduceStockNum(final int productId, final int warehouseId,  
			final int number, final int level, final String enterpriseCode) throws DaoException {
		
		final StringBuilder sql = new StringBuilder("UPDATE  ").append(getTname(enterpriseCode, TableEnum.WAREHOUSE_STOCK));
		if(level==1) { // 单品数量
			sql.append(" SET _number = ?");
		}else{
			sql.append(" SET _number = _number - ?");
		}
		sql.append(" WHERE _product_id = ? AND _warehouse_id = ? AND _level = ?");
		Object[] params = null;
		int[] argTypes = null;
		params = new Object[] {number, productId, warehouseId, level};
		argTypes = new int[] {Types.INTEGER, Types.INTEGER, Types.INTEGER, Types.INTEGER};
		return jdbcTemplate.update(sql.toString(), params, argTypes);
	}

	/**
	 * (non-Javadoc)
	 *@Title: addStockNum
	 *@Description: 
	 *@see com.sigmatrix.sc.handheld.inter.dao.WarehouseStockDao#addStockNum(int, int, java.lang.String, int, int, java.lang.String)
	 *@author youjun
	 *@date 2017年8月8日 上午1:41:19
	 */
	@Override
	public int addStockNum(final int productId, final int warehouseId, final String batchCode, 
			final int singleNum, final int packNum, final String enterpriseCode) throws DaoException {
		final StringBuilder sql = new StringBuilder("UPDATE  ").append(getTname(enterpriseCode, TableEnum.WAREHOUSE_STOCK));
		sql.append(" SET _num_of_sp = _num_of_sp + ?, _num_of_box = _num_of_box + ?, _update_time = ?");
		sql.append(" WHERE _product_id = ? AND _warehouse_id = ?");
		Object[] params = null;
		int[] argTypes = null;
		if(StringUtils.isBlank(batchCode)) {
			sql.append(" AND (_product_batch_id IS NULL OR _product_batch_id = '')");
			params = new Object[] {singleNum, packNum, new Date(), productId, warehouseId};
			argTypes = new int[] {Types.INTEGER, Types.INTEGER, Types.TIMESTAMP, Types.INTEGER, Types.INTEGER};
		}else{
			sql.append(" AND _product_batch_id = ?");
			params = new Object[] {singleNum, packNum, new Date(), productId, warehouseId, batchCode};
			argTypes = new int[] {Types.INTEGER, Types.INTEGER, Types.TIMESTAMP, Types.INTEGER, Types.INTEGER, Types.VARCHAR};
		}
		return jdbcTemplate.update(sql.toString(), params, argTypes);
	}
	/**
	 * (non-Javadoc)
	 *@Title: findListByCondition
	 *@Description: 
	 *@see com.sigmatrix.sc.production.mgt.dao.WarehouseStockDao
	 *	#findListByCondition(com.sigmatrix.sc.production.mgt.entity.WarehouseStock)
	 *@author youjun
	 *@date 2017年6月14日 上午10:33:25
	 */
	@Override
	public List<WarehouseStock> findListByCondition(final WarehouseStock warehouseStock, final String enterpriseCode) throws DaoException {
		final StringBuilder sql = new StringBuilder("SELECT * FROM  ").append(getTname(enterpriseCode, TableEnum.WAREHOUSE_STOCK)).append(" ");
		final List<Object> paramList = new ArrayList<Object>();
		try {
			if(warehouseStock!=null) {
				sql.append(" WHERE 1=1");
				if(warehouseStock.getWarehouseId()!=null) {
					sql.append(" AND _warehouse_id = ?");
					paramList.add(warehouseStock.getWarehouseId());
				}
				if(warehouseStock.getProductId()!=null) {
					sql.append(" AND _product_id = ?");
					paramList.add(warehouseStock.getProductId());
				}
				if(warehouseStock.getLevel()!=null) {
					sql.append(" AND _level = ?");
					paramList.add(warehouseStock.getLevel());
				}
			}
			return jdbcTemplate.query(sql.toString(), paramList.toArray(), new WarehouseStockRowMapper());
		} catch (DataAccessException e) {
			throw new DaoException(e);
		}
	}

	/**
	 * @ClassName: WarehouseStockRowMapper
	 * @Description: 封装返回的实体
	 * @author youjun 
	 * @date 2017-06-14 09:50:39
	 */
	public class WarehouseStockRowMapper implements RowMapper<WarehouseStock> {
		@Override
		public WarehouseStock mapRow(final ResultSet resultSet, final int rowNum) throws SQLException {
			final WarehouseStock warehouseStock = new WarehouseStock();
            warehouseStock.setId(resultSet.getLong("_id"));
			warehouseStock.setChannelId(resultSet.getInt("_channel_id"));
			warehouseStock.setWarehouseId(resultSet.getInt("_warehouse_id"));
			warehouseStock.setProductId(resultSet.getInt("_product_id"));
			warehouseStock.setProductCode(resultSet.getString("_product_code"));
			warehouseStock.setNumber(resultSet.getInt("_number"));
			warehouseStock.setLevel(resultSet.getInt("_level"));
			warehouseStock.setRemarks(resultSet.getString("_remarks"));
            return warehouseStock;
		}
	}


	@Override
	public List<WarehouseStock> findCondition(String eseCode, Integer warehouseId, Integer productId, Integer dealearId)
			throws DaoException {
		final StringBuilder sql = new StringBuilder()
				.append("SELECT * FROM ")
				.append(getTname(eseCode, TableEnum.WAREHOUSE_STOCK))
				.append(" WHERE _warehouse_id = ").append(warehouseId)
				.append(" AND _product_id = ").append(productId)
				.append(" AND _channel_id = ").append(dealearId);
		LOGGER.info(sql.toString());
		return jdbcTemplate.query(sql.toString(), new WarehouseStockRowMapper());
	}

	@Override
	public int reduceStock(int productId, int warehouseId, int packlevel, int number, int dealerId, String enterpriseCode) throws DaoException {
		final StringBuilder sql = new StringBuilder("UPDATE  ").append(getTname(enterpriseCode, TableEnum.WAREHOUSE_STOCK));
		sql.append(" SET _number = _number - ?");
		sql.append(" WHERE _product_id = ? AND _warehouse_id = ? AND _level = ? AND _channel_id = ?");
		Object[] params = null;
		int[] argTypes = null;
		params = new Object[] {number, productId, warehouseId, packlevel,dealerId};
		argTypes = new int[] {Types.INTEGER, Types.INTEGER, Types.INTEGER, Types.INTEGER, Types.INTEGER};
		return jdbcTemplate.update(sql.toString(), params, argTypes);
	}


	
	
}
